package com.www.jpa.service;

import cn.hutool.core.codec.Base64;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.www.jpa.dao.BackupRecordRepository;
import com.www.jpa.entity.BackupRecord;
import com.xxx.annotation.TimeCost;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.sql.DataSource;
import javax.transaction.Transactional;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * <p>
 * .
 *
 * @author o_o
 * @date 2025-06-23
 */
@Service
@Transactional
@Slf4j
public class DatabaseRestoreService {

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private BackupRecordRepository backupRecordRepository;

    @Autowired
    private ObjectMapper objectMapper;

    @Autowired
    private DataSource dataSource;

    /**
     * 从备份恢复数据
     *
     * @param backupId 备份记录ID
     */
    @Transactional
    @TimeCost
    public void performRestore(Long backupId) {
        try {
            // 1. 获取备份记录
            BackupRecord record = backupRecordRepository.findById(backupId)
                    .orElseThrow(() -> new IllegalArgumentException("备份记录不存在"));

            // 2. 解析JSON数据
            Map<String, List<Map<String, Object>>> tableDataMap = objectMapper.readValue(
                    Base64.decodeStr(record.getDataJson()),
                    new TypeReference<Map<String, List<Map<String, Object>>>>() {
                    });

            // 3. 禁用外键约束（不同数据库语法不同）
            disableForeignKeyChecks();

            try {
                // 4. 按顺序清空表并恢复数据
                List<String> tableNames = Arrays.asList(record.getTablesIncluded().split(","));

                // 应该按照从叶子到根的顺序处理表（避免外键约束问题）
                Collections.reverse(tableNames);

                for (String tableName : tableNames) {
                    if (tableDataMap.containsKey(tableName)) {
                        // 清空表
                        truncateTable(tableName);

                        // 恢复数据
                        restoreTableData(tableName, tableDataMap.get(tableName));
                    }
                }
            } finally {
                // 5. 重新启用外键约束
                enableForeignKeyChecks();
            }

            log.info("数据恢复成功，备份ID: {}", backupId);
        } catch (Exception e) {
            log.error("数据恢复失败", e);
            throw new RuntimeException("数据恢复失败: " + e.getMessage(), e);
        }
    }

    /**
     * 禁用外键约束检查
     */
    private void disableForeignKeyChecks() {
        String sql = isOracleDatabase() ?
                "BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET CONSTRAINTS = DEFERRED'; END;" :
                "SET FOREIGN_KEY_CHECKS = 0";

        entityManager.createNativeQuery(sql).executeUpdate();
    }

    /**
     * 启用外键约束检查
     */
    private void enableForeignKeyChecks() {
        String sql = isOracleDatabase() ?
                "BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET CONSTRAINTS = IMMEDIATE'; END;" :
                "SET FOREIGN_KEY_CHECKS = 1";

        entityManager.createNativeQuery(sql).executeUpdate();
    }

    /**
     * 清空表数据
     */
    private void truncateTable(String tableName) {
        String sql = isOracleDatabase() ?
                "TRUNCATE TABLE " + tableName :
                "TRUNCATE TABLE " + tableName;

        entityManager.createNativeQuery(sql).executeUpdate();
    }

    /**
     * 恢复表数据
     */
//    private void restoreTableData(String tableName, List<Map<String, Object>> dataList) {
//        if (dataList.isEmpty()) return;
//
//        // 获取列名
//        List<String> columns = new ArrayList<>(dataList.get(0).keySet());
//
//        // 构建批量插入SQL
//        String sql = buildBatchInsertSql(tableName, columns);
//
//        // 执行批量插入
//        for (Map<String, Object> row : dataList) {
//            Query query = entityManager.createNativeQuery(sql);
//
//            for (int i = 0; i < columns.size(); i++) {
//                String column = columns.get(i);
//                Object value = row.get(column);
//                query.setParameter(i + 1, value);
//            }
//
//            query.executeUpdate();
//        }
//    }
    private void restoreTableData(String tableName, List<Map<String, Object>> dataList) {
        if (dataList.isEmpty()) return;

        // 获取列名
        List<String> columns = new ArrayList<>(dataList.get(0).keySet());
        // 构建批量插入SQL
        String sql = buildBatchInsertSql(tableName, columns);
        // 执行批量插入
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql))
        {
            connection.setAutoCommit(false);
            for (int i = 0; i < dataList.size(); i++) {
                for (int j = 0; j < columns.size(); j++) {
                    String column = columns.get(j);
                    Object value = dataList.get(i).get(column);
                    ps.setObject(j + 1, value);
                }
                ps.addBatch();
                if (i % 5000 == 0 &&  i != 0) {
                    log.info("开始插入{}条数据。。。", i);
                    ps.executeBatch();
                    connection.commit();
                }
            }
            // 最后插入剩余的数据
            ps.executeBatch();
            connection.commit();
            log.info("成功插入{}条数据", dataList.size());
        } catch (Exception e) {
            log.error("msg={}", e.getMessage(), e);
        }
    }

    /**
     * 构建批量插入SQL
     */
    private String buildBatchInsertSql(String tableName, List<String> columns) {
        String columnList = String.join(", ", columns);
        String placeholders = columns.stream()
                .map(c -> "?")
                .collect(Collectors.joining(", "));

        return String.format("INSERT INTO %s (%s) VALUES (%s)",
                tableName, columnList, placeholders);
    }

    /**
     * 判断是否为Oracle数据库
     */
    private boolean isOracleDatabase() {
        return false;
    }
}